﻿using System;
using System.Collections.Generic;
using System.Data;
using SdmxMl.Common;
using SdmxMl.Manager;
using SdmxMl.Structure;
using SdmxMl.GenericMetadata;
using System.IO;
using SdmxMl.SmsImportCfg;

namespace PluginMeb
{
    class ImportShis
    {
        public const string Agency = "CH1_SHIS";
        public const string KEY_YEAR = "YEAR";
        public const string KEY_UNI_ID = "UNIID";
        public const string KEY_STUFE_ID = "STUID";
        public const string KEY_FA4_1 = "FA4_1";
        public const string KEY_FA4_2 = "FA4_2";
        public const string KEY_FA4_RES = "FA4_RES";

        private MetadataSetType metadataSet;
        private MetadataStructureDefinitionType msd;
        private SmManager sm;
        private ExcelDataReader.ExcelDataReader spreadsheetConcept = null;

        public static MetadataSetType CreateMetaset()
        {
            MetadataSetType ms = new MetadataSetType();
            ms.AgencyId = Agency;
            ms.Id = "MS_VERBUND";
            ms.MetadataStructureRef.AgencyId = Agency;
            ms.MetadataStructureRef.Id = "MSD_VERBUNDSTUDIUM";
            ms.Name.UpdateDefaultText("Verbundstudium");
            ms.AttributeValueSetList.Clear();
            ms.ReportRef = "R_VB";
            return ms;
        }

        private ExcelDataReader.ExcelDataReader LoadSources(string propertyPath)
        {
            ExcelDataReader.ExcelDataReader spreadsheet = null;
            if (propertyPath == null)
                throw new ApplicationException("Please provides path.");

            FileStream fs = null;
            try
            {
                fs = new FileStream(propertyPath, FileMode.Open, FileAccess.Read, FileShare.Read);
                spreadsheet = new ExcelDataReader.ExcelDataReader(fs);
            }
            finally
            {
                if (fs != null)
                    fs.Close();
            }

            return spreadsheet;
        }
        public void ImportFromExcel()
        {
            spreadsheetConcept = LoadSources(@"C:\Doc\SHIS\Mappe.xls");

            sm = new SmManager();

            // Fetch MSD and report for SAKE quest
            metadataSet = CreateMetaset();
            msd = sm.GetArtefact(SdmxArtefactType.MetadataStructureDefinitions,
               metadataSet.MetadataStructureRef, true) as MetadataStructureDefinitionType;
            ReportStructureType report = msd.ReportStructureList[0];

            DataTable dt = spreadsheetConcept.WorkbookData.Tables[0];
            WorkSheetHelper wh = new WorkSheetHelper(dt);

            for (int row = 2; row <= dt.Rows.Count; row++)
            {
                AttributeValueSetType atv = new AttributeValueSetType(msd, report, metadataSet);
                // The target values
                atv.TargetValuesTypeList.SetComponentValue(KEY_YEAR, wh.GetCellContent(row, 1));
                atv.TargetValuesTypeList.SetComponentValue(KEY_UNI_ID, wh.GetCellContent(row, 2));
                atv.TargetValuesTypeList.SetComponentValue(KEY_STUFE_ID, wh.GetCellContent(row, 3));
                atv.TargetValuesTypeList.SetComponentValue(KEY_FA4_1, wh.GetCellContent(row, 4));
                atv.TargetValuesTypeList.SetComponentValue(KEY_FA4_2, wh.GetCellContent(row, 5));
                atv.TargetValuesTypeList.SetComponentValue(KEY_FA4_RES, wh.GetCellContent(row, 6));

                // The reported attributes (no comment at creation)
                atv.ReportedAttributeList.Clear();

                metadataSet.AttributeValueSetList.Add(atv);
            }

            SmManager smms = new SmManager();

            smms.PutArtefact(SdmxArtefactType.MetadataSet, metadataSet);
            object ob = NavigationManager.LoadFromSmManager(smms);
            // NavigationManager.RequestNavigation(ob);

        }
    }
}
